package cn.auction.dao.impl;

import cn.auction.dao.AuctionDao;
import cn.auction.entity.Auction;
import cn.auction.entity.AuctionPage;
import cn.auction.entity.Auctionrecord;
import cn.auction.entity.EndAuction;
import my.framework.dao.CommonDao;
import my.framework.dao.DatabaseUtil;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class AuctionDaoImpl implements AuctionDao {
    CommonDao dao=new CommonDao();

    @Override
    public List<Auction> showAllAuctio(String newSql) throws SQLException {
        String sql="select * from auction";
        if(newSql!=null){
            sql="select au.auctionId,au.auctionName,au.auctionStartTime,au.auctionEndTime,au.auctionStartPrice from auction au,auctionrecord ad where ad.auctionId=au.auctionId and au.auctionEndTime>now() GROUP BY au.auctionId";
        }
        List<Auction> listAuctionA=null;
        try {
            listAuctionA=dao.executeQuery(Auction.class,sql,null);
            DatabaseUtil.getConnection().commit();
        } catch (SQLException throwables) {
            DatabaseUtil.getConnection().rollback();
            throwables.printStackTrace();
        }finally {
            DatabaseUtil.closeConnection();
        }
        return listAuctionA;
    }

    @Override
    public AuctionPage<Auction> showAllAuction(Auction condition, int pageIndex) throws SQLException {
        AuctionPage<Auction> pageInfo=new AuctionPage<Auction>();
        StringBuilder sqlBuilder=new StringBuilder("from auction where 1=1");
        List<Object> parms=new ArrayList<Object>();
        if(condition.getAuctionName()!=null&&!condition.getAuctionName().equals("")){
                sqlBuilder.append(" and auctionName like ? ");
                parms.add("%"+condition.getAuctionName()+"%");
        }
        if(condition.getAuctionDesc()!=null&&!condition.getAuctionDesc().equals("")){
                sqlBuilder.append(" and auctionDesc like ? ");
                parms.add("%"+condition.getAuctionDesc()+"%");
        }
        if(condition.getAuctionStartTime()!=null&&!condition.getAuctionStartTime().equals("")){
                sqlBuilder.append(" and auctionStartTime >=? ");
                parms.add(new Timestamp(condition.getAuctionStartTime().getTime()));
        }
        if(condition.getAuctionEndTime()!=null&&!condition.getAuctionEndTime().equals("")){
            sqlBuilder.append(" and auctionEndTime<=? ");
            parms.add(new Timestamp(condition.getAuctionEndTime().getTime()));
        }
        if(condition.getAuctionStartPrice()!=null&&!condition.getAuctionStartPrice().equals("")){
            sqlBuilder.append(" and auctionStarPrice>=? ");
            parms.add(condition.getAuctionStartPrice());
        }
        //总记录数
        int count=dao.executeQuery(Integer.class,"select count(auctionId)"+sqlBuilder.toString(),parms.toArray()).get(0);
        pageInfo.setCount(count);
        //当前页码
        pageInfo.setPageIndex(pageIndex);
        sqlBuilder.append(" order by auctionStartTime desc ");
        sqlBuilder.append(" limit ?,? ");
        parms.add((pageIndex-1)*AuctionPage.PAGESIZE);
        parms.add(AuctionPage.PAGESIZE);
        System.out.println("SQL"+sqlBuilder);
        List<Auction> list=dao.executeQuery(Auction.class,"select *"+sqlBuilder.toString(),parms.toArray());
        pageInfo.setPageList(list);
       return pageInfo;

    }

    @Override
    public int AddAuction(Auction auction) throws SQLException {
        String sql="insert into auction (auctionName,auctionStartPrice,auctionUpset,auctionStartTime,auctionEndTime,auctionPic,auctionPicType,auctionDesc) values(?,?,?,?,?,?,?,?)";
        int result=0;
        try {
            result=dao.executeUpdate(sql,new Object[]{auction.getAuctionName(),auction.getAuctionStartPrice(),auction.getAuctionUpset(),auction.getAuctionStartTime(),auction.getAuctionEndTime(),auction.getAuctionPic(),auction.getAuctionPicType(),auction.getAuctionDesc()});
            DatabaseUtil.getConnection().commit();
        } catch (SQLException throwables) {
            DatabaseUtil.getConnection().rollback();
            throwables.printStackTrace();
        }finally {
            DatabaseUtil.closeConnection();
        }
        return result;
    }

    @Override
    public Auction ShowAuctionById(int id) throws SQLException {
        String sql="SELECT * FROM auction where auctionId=?";
        List<Auction> auction=null;
        try {
            auction=dao.executeQuery(Auction.class,sql,new Object[]{id});
            DatabaseUtil.getConnection().commit();
        } catch (SQLException throwables) {
            DatabaseUtil.getConnection().rollback();
            throwables.printStackTrace();
        }finally {
            DatabaseUtil.closeConnection();
        }
        return auction.get(0);
    }

    @Override
    public int UpdateAuction(Auction auction) throws SQLException {
        String sql="update auction set auctionName=?,auctionStartPrice=?,auctionUpset=?,auctionStartTime=?,auctionEndTime=?,auctionPic=?,auctionPicType=?,auctionDesc=? where auctionId=?";
        int result=0;
        try {
          result=dao.executeUpdate(sql,new Object[]{auction.getAuctionName(),auction.getAuctionStartPrice(),auction.getAuctionUpset(),auction.getAuctionStartTime(),auction.getAuctionEndTime(),auction.getAuctionPic(),auction.getAuctionPicType(),auction.getAuctionDesc(),auction.getAuctionId()});
            DatabaseUtil.getConnection().commit();
        } catch (SQLException throwables) {
            DatabaseUtil.getConnection().rollback();
            throwables.printStackTrace();
        }finally {
            DatabaseUtil.closeConnection();
        }
        return result;
    }

    @Override
    public int deleteAuction(int auctionId) throws SQLException {
        String sql="delete from auction where auctionId=?";
        int result=0;
        try {
           result=dao.executeUpdate(sql,new Object[]{auctionId});
            DatabaseUtil.getConnection().commit();
        } catch (SQLException throwables) {
            DatabaseUtil.getConnection().rollback();
            throwables.printStackTrace();
        }finally {
            DatabaseUtil.closeConnection();
        }
        return result;
    }

    @Override
    public List<Auctionrecord> showAuctionrecord(int auctionId) throws SQLException {
        String sql="SELECT ad.auctionTime,ad.auctionPrice,auser.userName FROM `auctionrecord` ad,auctionuser auser,auction au\n" +
                "where ad.userId=auser.userId and ad.auctionId=au.auctionId and au.auctionId=? ORDER BY ad.auctionPrice desc";
        List<Auctionrecord> auctionrecordList=null;
        try {
            auctionrecordList=dao.executeQuery(Auctionrecord.class,sql,new Object[]{auctionId});
            DatabaseUtil.getConnection().commit();
        } catch (SQLException throwables) {
            DatabaseUtil.getConnection().rollback();
            throwables.printStackTrace();
        }finally {
            DatabaseUtil.closeConnection();
        }
        return auctionrecordList;
    }

    @Override
    public int AddAuctionrecord(int userId, int auctionId, double auctionPrice) throws SQLException {
        String sql="insert into auctionrecord (userId,auctionId,auctionTime,auctionPrice) values(?,?,now(),?)";
        int resutl=0;
        try {
            resutl=dao.executeUpdate(sql,new Object[]{userId,auctionId,auctionPrice});
            DatabaseUtil.getConnection().commit();
        } catch (SQLException throwables) {
            DatabaseUtil.getConnection().rollback();
            throwables.printStackTrace();
        }finally {
            DatabaseUtil.closeConnection();
        }
        return resutl;
    }

    @Override
    public List<EndAuction> showEndTimeAuction() throws SQLException {
        String sql="SELECT au.auctionName,au.auctionStartTime,au.auctionEndTime,au.auctionStartPrice,max(ad.auctionPrice) as auctionPrice,auser.userName FROM `auctionrecord` ad,auctionuser auser,auction au where ad.userId=auser.userId and ad.auctionId=au.auctionId and au.auctionEndTime<now() GROUP BY au.auctionId";
        List<EndAuction> endAuctionrecord=null;
        try {
            endAuctionrecord=dao.executeQuery(EndAuction.class,sql,new Object[]{});
            DatabaseUtil.getConnection().commit();
        } catch (SQLException throwables) {
            DatabaseUtil.getConnection().rollback();
            throwables.printStackTrace();
        }finally {
            DatabaseUtil.closeConnection();
        }
        return endAuctionrecord;
    }

    @Override
    public List<EndAuction> showAuctionNowTime() throws SQLException {
        String sql="SELECT au.auctionId,au.auctionName,auser.userName,MAX(ad.auctionPrice) as auctionPrice  FROM `auctionrecord` ad,auctionuser auser,auction au where ad.userId=auser.userId and ad.auctionId=au.auctionId and au.auctionEndTime>now() GROUP BY au.auctionId,auser.userId ORDER BY ad.auctionPrice desc";
        List<EndAuction> listEndAuctionName=null;
        try {
            listEndAuctionName=dao.executeQuery(EndAuction.class,sql,new Object[]{});
            DatabaseUtil.getConnection().commit();
        } catch (SQLException throwables) {
            DatabaseUtil.getConnection().rollback();
            throwables.printStackTrace();
        }finally {
            DatabaseUtil.closeConnection();
        }
        return listEndAuctionName;
    }
}
